Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Indexes and sorting

You create and maintain all indexes from within the MSS data source using native tools, rather than with the Data Dictionary. A data source index uses a logical pointer to the physical locations of table rows in order to sequence data access. You can add and drop indexes but you cannot use their names in queries. The data source alone ultimately decides when and how to use indexes; its decisions are not affected by the DataServer.

Give careful consideration to benefit and cost of creating indexes. Having indexes for frequently executed queries can greatly improve record retrieval performance. An abundance of unused or infrequently used indexes can have a negative impact on performance due to the overhead cost of maintaining the indexes.

Using index definitions in the MSS data source, the DataServer builds index information in the schema holder. OpenEdge index definitions for the data source schema serve two purposes:

They allow you to use the OF option in Progress 4GL with the FOR EACH and FIND statements. Using the OF option improves the readability of your code. The OF keyword is equivalent to the SQL WHERE clause. You can use OF only when you have a field of the same name in two tables and the field is an index in at least one of the tables. Therefore, since the cust-num field is common to both the order and customer tables, you could write the following statement:

FOR EACH order OF customer:

OpenEdge translates USE–INDEX to SQL ORDER BY for DataServer operations. SQL Server uses the ORDER BY clause to assist in selecting the optimal index for the query. For example, if you define city-dept as an MSS data source primary key on the city and department fields, it is a unique index in the schema holder. In this case, the following Progress statements are equivalent when accessing the data source:

FOR EACH employee USE-INDEX city-dept:

FOR EACH employee BY city BY department:

Note: If you do not specify a USE-INDEX or BY clause, your query will return records in an unpredictable order. If your application requires a predictable order, use include a USE-INDEX or BY clause.

USE-INDEX and BY clause considerations

The BY clause offers greater programming flexibility than the USE-INDEX clause, and should be used in most cases. If indexes are added or deleted, applications coded with the BY clause only require a recompile to adjust to the index changes. Applications coded with the USE-INDEX clause require code modifications to be current with the index changes. If the index specified by a USE-INDEX clause is dropped, the code will fail to compile until the code is modified. If there is a more efficient index than the one specified in the USE-INDEX clause, it will not be selected by the 4GL and it is unlikely to be used in the data source’s query plan.

Dummy indexes for sort order

You can add dummy index definitions to your schema holder independent of the actual index definitions in your MSS data source to provide a sort order. These dummy indexes can improve the readability of your Progress 4GL code. An index in the schema holder need not match an index in the MSS data source. However, the absence of an index definition in the data source can impact performance when you retrieve data with selection on the dummy indexes.

Unique indexes

If your MSS data source tables have at least one unique index, they can be used to support operations such as backward and forward scrolling and accurate cursor positioning through the FIND CURRENT, PREV, and LAST statements. If a table does not have a unique index, you can only scroll forward through its data.

If an MSS data source table does not have a unique index, you can designate an index to serve as the unique index for the schema holder. An index that you designate as unique in the schema holder must be unique with respect to the data in the data source, otherwise you receive run-time errors. See Chapter 7, "The DataServer Tutorial," for instructions on using the Data Dictionary to designate unique indexes.

MSS data source views and result sets from stored procedures do not have unique indexes. Just as for tables, you can use the Data Dictionary to create a unique index in the schema holder based on fields in a view or result set so that you can browse data accessed through views or stored procedures.For more information about views, see the "MSS data source views" section.

Note: Do not change the designated ROWID key of a record while an application is running. Suppose, for example, that cust-num is a unique key and has been designated the Progress ROWID. If a user changes the value of cust-num for a customer from 1 to 111, other users receive an error message when they try to access the record for customer 1.